﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{    
    public partial class YGRVV05Data : BaseFunction
    {
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        /*public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }

        SETINTEXDB INTEXDB = new SETINTEXDB();
         */
        public YGRVV05Data()
            : base()
        {
        }
         public YGRVV05Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVV05(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VV05";
            string FROMDATE = dwTABEL["FROMDATE"].ToString().Trim();
            string ENDDATE = dwTABEL["ENDDATE"].ToString().Trim();
            string DNNO = dwTABEL["DNNO"].ToString().Trim();
            string CUSTOMER =Right("00000000"+dwTABEL["CUSTOMER"].ToString().Trim(),8);
            byte[] x;
            x = Encoding.GetEncoding("gb2312").GetBytes(dwTABEL["CUSTOMER"].ToString().Trim());
            string CUST = Encoding.GetEncoding("ISO-8859-1").GetString(x);
            try
            {
                /*" SELECT A.VVNEUDAT AS NEUDAT,0 AS ENDDAT,TO_NUMBER(A.VVLIEFSCH) AS DNNO, "
                    + " A.VVSTATUS,TO_NUMBER(A.VVAVISNR) AS SHIPNO, "
                    + " A.VVEBDAT,TRIM(A.VVORTU) AS DRIVER,TRIM(A.VVBZBEZU) AS CAR,TO_NUMBER(A.VVSEQUNO) AS UPS, "
                    + " TRIM(H.VBEZ) AS CARRIER,TRIM(H.VTEL) AS TEL, "
                    + " B.VVTAUFKRS||'-'||TO_NUMBER(B.VVTAUFLFD)||'-'||TO_NUMBER(B.VVTAUFPOS) AS CO, "
                    + " (CASE WHEN TRIM(L.VKKBESTNR)=TRIM(C.VKPKDKOMM) THEN '' ELSE TRIM(L.VKKBESTNR) END) AS REQORD, "
                    + " C.VKPKDKOMM AS PO,TRIM(C.VKPFARBE)||'('||TRIM(C.VKPKDFARBNR)||')' AS COLOR,C.VKPPREIS*0.001 AS PRICE, "
                    + " nvl((G.VKZZUABWERT1+G.VKZZUABWERT2+G.VKZZUABWERT3+G.VKZZUABWERT4+G.VKZZUABWERT5)*0.01,0) AS SURC, "
                    + " '' AS SURCHARGE,  "
                    + " TRIM(NVL(K.KDANAME1,D.KDKNAME)) AS REC, "
                    + " NVL(TRIM(K.KDASTRASSE1)||TRIM(K.KDASTRASSE2),TRIM(D.KDSTRASSE1)||TRIM(D.KDSTRASSE2)) AS RECADDR, "
                    + " TRIM(F.GPSPPART) AS SPPART,TRIM(F.GPFBPART) AS FBPART,TRIM(M.GLBEM) AS BEM, "
                    + " E.ZUEINTKG*0.001 AS ALLQTY,SUM(1) AS QTY,E.ZUEINTKG*0.001*SUM(1) as TOTNET, "
                    + " E.ZUEINTKG*0.001+0.4 AS KG,(E.ZUEINTKG*0.001+0.4)*SUM(1) AS TOTKG,0 as VALUE  "
                    + " FROM VVLIEF A,VVTLIEF B,VKPOS C,KDSTAMM D,ZUTEI E,GPDAT F, "
                    + " VKZUAB G,VERSART H,KUNDADR K,VKKOPF L,GLLG M "
                    + " WHERE A.VVMANDANT = B.VVTMANDANT AND A.VVLIEFSCH = B.VVTLIEFSCH "
                    + " AND B.VVTMANDANT = C.VKPMANDANT AND B.VVTAUFKRS = C.VKPAUFKRS "
                    + " AND B.VVTAUFLFD = C.VKPAUFLFD AND B.VVTAUFPOS = C.VKPAUFPOS "
                    + " AND C.VKPSA ='  ' AND C.VKPMANDANT = D.KDMANDANT "
                    + " AND C.VKPKUNDNR = D.KDKUNDNR AND C.VKPMANDANT = L.VKKMANDANT "
                    + " AND C.VKPSA = L.VKKSA AND C.VKPAUFKRS = L.VKKAUFKRS "
                    + " AND C.VKPAUFLFD = L.VKKAUFLFD AND B.VVTMANDANT = E.ZUMANDANT "
                    + " AND B.VVTAUFKRS = E.ZUAUFKRS AND B.VVTAUFLFD = E.ZUAUFLFD "
                    + " AND B.VVTAUFPOS = E.ZUAUFPOS AND B.VVTTEILNR = E.ZUTEILNR "
                    + " AND E.ZUMANDANT = M.GLMANDANT AND E.ZUPARTIE = M.GLPARTIE "
                    + " AND E.ZUPARTIEKZ = M.GLPARTIEKZ AND E.ZULBEHI = M.GLLBEHI "
                    + " AND E.ZUMANDANT = F.GPMANDANT  "
                    + " AND E.ZUPARTIE = F.GPPARTIE AND E.ZUPARTIEKZ = F.GPPARTIEKZ "
                    + " AND C.VKPMANDANT = G.VKZMANDANT(+) AND C.VKPSA = G.VKZSA(+) "
                    + " AND C.VKPAUFKRS = G.VKZAUFKRS(+) AND C.VKPAUFLFD = G.VKZAUFLFD(+) "
                    + " AND C.VKPAUFPOS = G.VKZAUFPOS(+) AND A.VVMANDANT = H.VMANDANT(+) "
                    + " AND A.VVVERSART = H.VVERSART(+) AND A.VVSPED = H.VSPED(+) "
                    + " AND A.VVMANDANT = K.KDAMANDANT(+) AND A.VVKUNDNRE = K.KDAKUNDNR(+) "
                    + " AND A.VVADRSLL = K.KDAADRSL(+)  "
                    + " AND A.VVNEUDAT>='" + FROMDATE + "' AND A.VVNEUDAT <='"+ENDDATE+"' "
                    + " AND (TO_NUMBER(A.VVKUNDNRE) = '" + CUSTOMER + "' OR  '" + CUSTOMER + "' = 'AAA') "
                    + " AND (TO_NUMBER(A.VVLIEFSCH) = '" + DNNO + "' OR  '" + DNNO + "' = 'AAA') "
                    + " GROUP BY A.VVNEUDAT ,TO_NUMBER(A.VVLIEFSCH),A.VVSTATUS,TO_NUMBER(A.VVAVISNR), "
                    + " A.VVEBDAT,TRIM(A.VVORTU),TRIM(A.VVBZBEZU),TO_NUMBER(A.VVSEQUNO), "
                    + " TRIM(H.VBEZ),TRIM(H.VTEL), "
                    + " B.VVTAUFKRS||'-'||TO_NUMBER(B.VVTAUFLFD)||'-'||TO_NUMBER(B.VVTAUFPOS), "
                    + " (CASE WHEN TRIM(L.VKKBESTNR)=TRIM(C.VKPKDKOMM) THEN '' ELSE TRIM(L.VKKBESTNR) END) , "
                    + " C.VKPKDKOMM,TRIM(C.VKPFARBE)||'('||TRIM(C.VKPKDFARBNR)||')',C.VKPPREIS*0.001 , " 
                    + " (G.VKZZUABWERT1+G.VKZZUABWERT2+G.VKZZUABWERT3+G.VKZZUABWERT4+G.VKZZUABWERT5)*0.01, " 
                    + " TRIM(NVL(K.KDANAME1,D.KDKNAME)), " 
                    + " NVL(TRIM(K.KDASTRASSE1)||TRIM(K.KDASTRASSE2),TRIM(D.KDSTRASSE1)||TRIM(D.KDSTRASSE2)), " 
                    + " TRIM(F.GPSPPART),TRIM(F.GPFBPART),TRIM(M.GLBEM),E.ZUEINTKG*0.001 " 
                    + " UNION ALL " 
                 */
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT 0 AS POS,A.VVNEUDAT AS NEUDAT, "
                    + " substr(V.vvaversdat,1,4)||'-'||substr(V.vvaversdat,5,2)||'-'||substr(V.vvaversdat,7,2) AS ENDDAT,TO_NUMBER(A.VVLIEFSCH) AS DNNO, " 
                    + " A.VVSTATUS,TO_NUMBER(A.VVAVISNR) AS SHIPNO, " 
                    + " A.VVEBDAT,TRIM(A.VVORTU) AS DRIVER,TRIM(A.VVBZBEZU) AS CAR,TO_NUMBER(A.VVSEQUNO) AS UPS, " 
                    + " TRIM(H.VBEZ) AS CARRIER,TRIM(H.VTEL) AS TEL, " 
                    + " B.VVTAUFKRS||'-'||TO_NUMBER(B.VVTAUFLFD)||'-'||TO_NUMBER(B.VVTAUFPOS) AS CO, "
                    + " (CASE WHEN TRIM(L.VKKBESTNR)=TRIM(C.VKPKDKOMM) THEN '' ELSE TRIM(C.VKPKDKOMM) END) AS REQORD, "
                    + " TRIM(L.VKKBESTNR) AS PO,TRIM(C.VKPFARBE)||'('||TRIM(C.VKPKDFARBNR)||')' AS COLOR,C.VKPPREIS*0.001 AS PRICE, "
                    + "substr(C.VKPLIEFTERM,1,4)||'-'||substr(C.VKPLIEFTERM,5,2)||'-'||substr(C.VKPLIEFTERM,7,2) AS DELIVERY, " 
                    + " nvl((G.VKZZUABWERT1+G.VKZZUABWERT2+G.VKZZUABWERT3+G.VKZZUABWERT4+G.VKZZUABWERT5)*0.01,0) AS SURC, " 
                    + " '' AS SURCHARGE,  "                   
                    + " TRIM(D.KDKNAME) AS CUSTOMER, " 
                    + " NVL(TRIM(K.KDASTRASSE1)||TRIM(K.KDASTRASSE2),TRIM(D.KDSTRASSE1)||TRIM(D.KDSTRASSE2)) AS RECADDR, " 
                    + " TRIM(F.GPSPPART) AS SPPART,TRIM(F.GPFBPART) AS FBPART,TRIM(E.GPABEM) AS BEM, " 
                    + " E.GPAKG*0.001 AS ALLQTY,sum(1) AS QTY,E.GPAKG*0.001*SUM(1) AS TOTNET, "
                    + " E.GPAKG*0.001+0.4 AS KG,(E.GPAKG*0.001+0.4)*SUM(1) AS TOTKG,0 as VALUE " 
                    + " FROM VVLIEF A,VVTLIEF B,VKPOS C,KDSTAMM D,GPACK E,GPDAT F, " 
                    + " VKZUAB G,VERSART H,KUNDADR K,VKKOPF L,VVAVIS V " 
                    + " WHERE A.VVMANDANT = B.VVTMANDANT AND A.VVLIEFSCH = B.VVTLIEFSCH " 
                    + " AND B.VVTMANDANT = C.VKPMANDANT AND B.VVTAUFKRS = C.VKPAUFKRS " 
                    + " AND B.VVTAUFLFD = C.VKPAUFLFD AND B.VVTAUFPOS = C.VKPAUFPOS " 
                    + " AND C.VKPSA ='  ' AND C.VKPMANDANT = D.KDMANDANT " 
                    + " AND C.VKPKUNDNR = D.KDKUNDNR AND C.VKPMANDANT = L.VKKMANDANT " 
                    + " AND C.VKPSA = L.VKKSA AND C.VKPAUFKRS = L.VKKAUFKRS " 
                    + " AND C.VKPAUFLFD = L.VKKAUFLFD AND B.VVTMANDANT = E.GPAMANDANT " 
                    + " AND B.VVTAUFKRS = E.GPAAUFKRS AND B.VVTAUFLFD = E.GPAAUFLFD " 
                    + " AND B.VVTAUFPOS = E.GPAAUFPOS AND B.VVTTEILNR = E.GPATEILNR " 
                    + " AND B.VVTLIEFSCH = E.GPALIEFSCH AND E.GPAMANDANT = F.GPMANDANT  " 
                    + " AND E.GPAPARTIE = F.GPPARTIE AND E.GPAPARTIEKZ = F.GPPARTIEKZ " 
                    + " AND C.VKPMANDANT = G.VKZMANDANT(+) AND C.VKPSA = G.VKZSA(+) " 
                    + " AND C.VKPAUFKRS = G.VKZAUFKRS(+) AND C.VKPAUFLFD = G.VKZAUFLFD(+) " 
                    + " AND C.VKPAUFPOS = G.VKZAUFPOS(+) AND A.VVMANDANT = H.VMANDANT(+) " 
                    + " AND A.VVVERSART = H.VVERSART(+) AND A.VVSPED = H.VSPED(+) " 
                    + " AND A.VVMANDANT = K.KDAMANDANT(+) AND A.VVKUNDNRE = K.KDAKUNDNR(+) " 
                    + " AND A.VVADRSLL = K.KDAADRSL(+) AND A.VVMANDANT = V.VVAMANDANT "
                    + " AND A.VVAVISNR = V.VVAAVISNR "
                    + " AND V.VVAVERSDAT >='" + FROMDATE + "' AND V.VVAVERSDAT <='" + ENDDATE + "' "
                    + " AND (A.VVKUNDNRE = '" + CUSTOMER + "' OR D.KDKNAME LIKE '%" + CUST + "%' OR  '" + CUSTOMER + "' = '00000AAA') "
                    + " AND (TO_NUMBER(A.VVLIEFSCH) = '" + DNNO + "' OR  '" + DNNO + "' = 'AAA') "
                    + " GROUP BY A.VVNEUDAT,V.VVAVERSDAT ,TO_NUMBER(A.VVLIEFSCH),A.VVSTATUS,TO_NUMBER(A.VVAVISNR), " 
                    + " A.VVEBDAT,TRIM(A.VVORTU),TRIM(A.VVBZBEZU),TO_NUMBER(A.VVSEQUNO), " 
                    + " TRIM(H.VBEZ),TRIM(H.VTEL), " 
                    + " B.VVTAUFKRS||'-'||TO_NUMBER(B.VVTAUFLFD)||'-'||TO_NUMBER(B.VVTAUFPOS), "
                    + " (CASE WHEN TRIM(L.VKKBESTNR)=TRIM(C.VKPKDKOMM) THEN '' ELSE TRIM(C.VKPKDKOMM) END), "
                    + " TRIM(L.VKKBESTNR),TRIM(C.VKPFARBE)||'('||TRIM(C.VKPKDFARBNR)||')',C.VKPPREIS*0.001 , C.VKPLIEFTERM ," 
                    + " (G.VKZZUABWERT1+G.VKZZUABWERT2+G.VKZZUABWERT3+G.VKZZUABWERT4+G.VKZZUABWERT5)*0.01, " 
                    + " TRIM(D.KDKNAME), " 
                    + " NVL(TRIM(K.KDASTRASSE1)||TRIM(K.KDASTRASSE2),TRIM(D.KDSTRASSE1)||TRIM(D.KDSTRASSE2)), " 
                    + " TRIM(F.GPSPPART),TRIM(F.GPFBPART),TRIM(E.GPABEM),E.GPAKG*0.001 "
                    + " ORDER BY ENDDAT,DNNO,CO,SPPART,ALLQTY DESC "                   
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    string dn = "",co="",dn1="",co1="",surc = "",SUR = "";
                    decimal S1=0, TOTNET=0, TOT=0,PRICE =0;
                    int I = 1;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DRIVER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DRIVER"] = value;                      
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CAR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CAR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CARRIER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CARRIER"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COLOR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CUSTOMER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CUSTOMER"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["RECADDR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["RECADDR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEM"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SPPART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SPPART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["FBPART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["FBPART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["PO"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["PO"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REQORD"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REQORD"] = value;
                        dn = dw["DNNO"].ToString().Trim();
                        co = dw["CO"].ToString().Trim();
                        surc = dw["SURC"].ToString().Trim();
                        if(surc != "0")
                        {
                            if (dn != dn1 || co != co1)
                            {
                                string SURCHARGE = GetSURCHARGE(dw);
                                dw["SURCHARGE"] = SURCHARGE;
                                dn1 = dn; co1 = co;
                            }
                        }
                        TOTNET = System.Convert.ToDecimal(dw["TOTNET"].ToString().Trim());
                        PRICE = System.Convert.ToDecimal(dw["PRICE"].ToString().Trim());
                        SUR = dw["SURCHARGE"].ToString().Trim();
                        if (SUR == "")
                        {
                            S1 = 0;
                        }
                        else
                        {
                            S1 = System.Convert.ToDecimal(SUR);
                        }
                        TOT = TOTNET* PRICE + S1;
                        dw["VALUE"] = TOT;
                        dw["POS"] = I;
                        I = I + 1;
                    }
                }
                 
                //DataSet set = new DataSet();
                //foreach (DataRow row in Result.Tables[0].Rows)
                //{
                //    string SURCHARGE = GetSURCHARGE(row);
                //    row["SURCHARGE"] = SURCHARGE;
                //}
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public string GetSURCHARGE(DataRow dwABPOS)
        {
            string SURCHARGE = "";
            string DNNO = dwABPOS["DNNO"].ToString().Trim();
            string CO = dwABPOS["CO"].ToString().Trim();
            string SURC = dwABPOS["SURC"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT 'A' AS A FROM VVTLIEF A "
                    + " WHERE A.VVTAUFKRS||'-'||TO_NUMBER(A.VVTAUFLFD)||'-'||TO_NUMBER(A.VVTAUFPOS) ='"+CO+"'  "
                    + " AND TO_NUMBER(A.VVTLIEFSCH) = '"+DNNO+"' "
                    + " AND A.VVTTEILNR IN  "
                    + " (SELECT MIN(VVTTEILNR) FROM VVTLIEF X  "
                    + " WHERE X.VVTAUFKRS||'-'||TO_NUMBER(X.VVTAUFLFD)||'-'||TO_NUMBER(X.VVTAUFPOS) ='" + CO + "') "
                    ;

                dsResult = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {                    
                    string A = "";
                    int i = 0;
                    foreach (DataRow dwTemp in dsResult.Tables[0].Rows)
                    {
                        A = dwTemp["A"].ToString().Trim();   
                        if (A=="A")
                        {
                            SURCHARGE = SURC;
                        }
                    } 
                    
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return SURCHARGE;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVV05(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

    }
}